package spectra.ee.web.kbhelper.service;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.collections.map.CaseInsensitiveMap;

import com.ibatis.sqlmap.client.SqlMapClient;
import spectra.base.log.Logger;
import spectra.base.sql.ibatis.SqlMapperWeb;
import spectra.base.util.DateUtil;
import spectra.ee.web.WebPublic;
import spectra.ee.web.common.service.AttachManager;
import spectra.ee.web.kb.model.TopKeyword;
import spectra.ee.web.kbhelper.model.KbHelper;

/**
 * 노드 관련 클래스.
 *
 * @author kmhan
 */
public class KbHelperServiceBase
{

    /** Logger객체. */
    private static Logger logger = Logger.getLoggerInstance(KbHelperServiceBase.class.getName());

    /** SqlMapClient 객체. */
    protected static SqlMapClient sqlMap = SqlMapperWeb.getSqlMapWebInstance();
        
    /**
     * 상담지식 TOP-N을 가져오는 메소드이다.
     * @param strDomainId
     * @param iCount
     * @param strTopNType
     * @return
     */
    public List selectTopnList(String strDomainId, int iCount, String strTopNType)
    {
        List list = null;
        try
        {
            KbHelper obj = new KbHelper();
            obj.setDomainId(strDomainId);
            obj.setEndNo(iCount);
            obj.setTopnType(strTopNType);
            obj.setCurrentDate(DateUtil.getCurrDateTimeStamp());
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setRowsPerPage(iCount);
            list = (List) sqlMap.queryForList("KbHelper.selectTopnList", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    public List selectNewKbList(int pageNo, int rowsPerPage, String domainId)
    {
    	return selectNewKbList(pageNo, rowsPerPage, domainId, null, null);
    }
    
    /**
     * 최근에 등록된 지식리스트를 가져오는 메소드이다.
     * @param strDomainId
     * @param iCount
     * @return
     */
    public List selectNewKbList(int pageNo, int rowsPerPage, String domainId, String condition, String searchText)
    {
        List listRet = null;
        try
        {
        	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
            KbHelper obj = new KbHelper();
            obj.setDomainId(domainId);
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setCurrentDate(currDate);
        	obj.setStartNo((pageNo-1) * rowsPerPage + 1);
       	 	obj.setEndNo(pageNo * rowsPerPage);
       	 	obj.setCondition(condition);
       	 	obj.setRowsPerPage(rowsPerPage);
       	 	if (searchText != null)
    	 	{
	       	 	searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
	       	 	searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
    	 	}
    	 	obj.setSearchText(searchText);
            
            listRet = (List) sqlMap.queryForList("KbHelper.selectNewKbList", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return listRet;
    }
    
    /**
     * 최근에 등록된 지식리스트 개수를 가져오는 메소드이다.
     * @param domainId
     * @param condition
     * @param searchText
     * @return
     */
    public int selectNewKbListCount(String domainId, String condition, String searchText)
    {
        int iResult = 0;
        try
        {
        	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
            KbHelper obj = new KbHelper();
            obj.setDomainId(domainId);  
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setCurrentDate(currDate);
       	 	obj.setCondition(condition);
       	 	if (searchText != null)
       	 	{
	       	 	searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
	       	 	searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
       	 	}
       	 	obj.setSearchText(searchText);
            
            iResult = ((Integer)sqlMap.queryForObject("KbHelper.selectNewKbListCount", obj)).intValue();
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return iResult;
    }
    
    /**
     * 최근등록지식의 이전글 가져오는 메소드이다.
     * @param domainId
     * @param kbId
     * @param createdDate
     * @return
     */
    public KbHelper selectPrevNewKb(String domainId, String kbId, String createdDate)
    {
        KbHelper kbHelperObj = null;
        try
        {
        	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
            KbHelper obj = new KbHelper();
            obj.setDomainId(domainId);
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setCurrentDate(currDate);
        	obj.setCreatedDate(createdDate);
        	obj.setKbId(kbId);
            
        	kbHelperObj = (KbHelper) sqlMap.queryForObject("KbHelper.selectPrevNewKb", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return kbHelperObj;
    }
    
    /**
     * 최근등록지식의 다음글 가져오는 메소드이다.
     * @param domainId
     * @param kbId
     * @param createdDate
     * @return
     */
    public KbHelper selectNextNewKb(String domainId, String kbId, String createdDate)
    {
        KbHelper kbHelperObj = null;
        try
        {
        	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
            KbHelper obj = new KbHelper();
            obj.setDomainId(domainId);
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setCurrentDate(currDate);
        	obj.setCreatedDate(createdDate);
        	obj.setKbId(kbId);
            
        	kbHelperObj = (KbHelper) sqlMap.queryForObject("KbHelper.selectNextNewKb", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return kbHelperObj;
    }
    
    /**
     * 노드ID, 지식ID로 지식의 상세 내용 가져오는 메소드.
     * @param kbId 지식ID.
     * @return List 지식ID로 가져온 내용 리스트. 
     */
     public KbHelper selectKbByKbId(String nodeId, String kbId)
     {
         KbHelper kbHelperObj = null;
         try
         {
        	 KbHelper obj = new KbHelper();
        	 obj.setNodeId(nodeId);
        	 obj.setKbId(kbId);
        	 obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
        	 kbHelperObj = (KbHelper) sqlMap.queryForObject("KbHelper.selectKbByKbId", obj);
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }
         return kbHelperObj;
     }
     
     /**
      * 노드ID, 지식ID로 지식의 상세 내용 가져오는 메소드.
      * @param kbId 지식ID.
      * @return List 지식ID로 가져온 내용 리스트. 
      */
      public KbHelper selectKb(String kbId)
      {
          KbHelper kbHelperObj = null;
          try
          {
        	  KbHelper obj = new KbHelper();
         	  obj.setKbId(kbId);
         	  obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
         	  kbHelperObj = (KbHelper) sqlMap.queryForObject("KbHelper.selectKb", obj);
         	 
         	  if (kbHelperObj != null)
	   		  {
         		  String contents = selectKbContents(kbId);
	   			  kbHelperObj.setContents(contents);
	   			 
	   			  if (kbHelperObj.getAttachCount() > 0)
	   			  {
	   				  List attachList = selectAttachObjectByKbId(kbId);
	   				  kbHelperObj.setAttachList(attachList);
	   			  }	   			  
	   		  }
          }
          catch (SQLException e)
          {
              logger.error(e.getMessage());
          }
          return kbHelperObj;
      }
    
    /**
     * 지식ID로 내용가져오는 메소드.
     *
     * @param strKbId 지식ID.
     * @return strContents FAQ내용.
     */
     public String selectKbContents(String strKbId)
     {
         String strContents = "";
         List list = null;
         try
         {
             list = (List)sqlMap.queryForList("KbHelper.selectKbContentsByKbId", strKbId);
             if (list != null && list.size() > 0)
             {
                 CaseInsensitiveMap map = null;

                 for (int i = 0; i < list.size(); i++)
                 {
                     map = (CaseInsensitiveMap)list.get(i);
                     strContents += map.get("CONTENTS");
                 }
             }
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }
         return strContents;
     }
     
     /**
      * nodeId, kbId로 KB에 대한 모든 정보를 가져온다. (t_kb, t_kb_detail, t_kb_contents, t_attach)
      * @param nodeId
      * @param kbId
      * @return
      */
     public KbHelper selectKbObjectByKbId(String nodeId, String kbId)
     {
    	 KbHelper kbHelperObj = null;
    	 try
    	 {
    		 kbHelperObj = selectKbByKbId(nodeId, kbId);
    		 if (kbHelperObj != null)
    		 {
    			 String contents = selectKbContents(kbId);
    			 kbHelperObj.setContents(contents);
    			  
    			 if (kbHelperObj.getAttachCount() > 0)
    			 {
    				 List attachList = selectAttachObjectByKbId(kbId);
    				 kbHelperObj.setAttachList(attachList);
    			 }
    		 }
    	 }
    	 catch (Exception e)
    	 {
    		 logger.error(e.getMessage());
    	 }
         return kbHelperObj;
     }
     
     /**
      * kbId로 첨부파일 정보를 가져오는 메소드
      * @param kbId
      * @return
      */
     public List selectAttachObjectByKbId(String kbId)
     {
    	 List attachList = null;
         try
         {
        	 AttachManager attachManager = new AttachManager();
        	 attachList = attachManager.selectAttachObjectByKbId(kbId);
         }
         catch (Exception e)
         {
             logger.error(e.getMessage());
         }
         return attachList;
     }
     
     /**
      * kbId로 지식의 조회를 업데이트 하는 메소드이다.
      * @param kbId
      * @return
      */
     public boolean updateKbHitCount(String kbId)
     {
         boolean result = false;
         KbHelper obj = new KbHelper();
         obj.setKbId(kbId);
         obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
         try
         {
             sqlMap.update("KbHelper.updateKbHitCount", obj);
             result = true;
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }         
         return result;
     }
     
     /**
      * kb조회에 대한 로그를 남기는 메소드이다.
      * @param logId
      * @param kbId
      * @param serviceType
      * @param nodeId
      * @return
      */
     public boolean insertKbViewLog(String kbId, String nodeId, String accountId, String logId)
     {
         boolean result = false;

         KbHelper obj = new KbHelper();
         obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
         obj.setKbId(kbId);
         obj.setLogId(logId);
         obj.setNodeId(nodeId);
         obj.setCreatedBy(accountId);
         obj.setCurrentDate(DateUtil.getCurrDateTimeStamp());

         try
         {
             sqlMap.update("KbHelper.insertKbViewLog", obj);
             result = true;
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }         
         return result;
     }
     
     /**
      * nodeId로 지식개수를 가져오는 메소드
      * @param nodeId
      * @return
      */
     public int selectKbListCount(String nodeId)
     {    	 
    	 int iResult = 0;
    	 try
         {
        	 KbHelper obj = new KbHelper();
        	 obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
        	 obj.setCurrentDate(DateUtil.getCurrDate("yyyyMMddHHmmss"));
        	 obj.setNodeId(nodeId);
        	 
        	 iResult = ((Integer)sqlMap.queryForObject("KbHelper.selectKbListCount", obj)).intValue();
         }
         catch (Exception e)
         {
             logger.error(e.getMessage());
         }         
         return iResult;
     }
     
     /**
      * nodeId로 지식리스트 가져오는 메소드
      * @param kbId
      * @return
      */
     public List selectKbList(int pageNo, int rowsPerPage, String nodeId, String pageSortType)
     {
    	 List list = null;
         try
         {
        	 KbHelper obj = new KbHelper();
        	 obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
        	 obj.setCurrentDate(DateUtil.getCurrDate("yyyyMMddHHmmss"));
        	 obj.setNodeId(nodeId);
        	 obj.setOrderBy(pageSortType);
        	 obj.setStartNo((pageNo-1) * rowsPerPage + 1);
        	 obj.setEndNo(pageNo * rowsPerPage);
        	 obj.setRowsPerPage(rowsPerPage);
        	 
        	 list = (List)sqlMap.queryForList("KbHelper.selectKbList", obj);
         }
         catch (Exception e)
         {
             logger.error(e.getMessage());
         }
         return list;
     }
     
     /**
      * TOP KEYWORD를 가져오는 메소드이다.
      * @param strDomainId
      * @param strTopKeywordType
      * @param iTopKeywordCount
      * @return
      */
     public List selectTopKeyword(String strDomainId, String strTopKeywordType, int iTopKeywordCount)
     {
         List list = null;
         try
         {
             TopKeyword topKeyword = new TopKeyword();
             topKeyword.setDomainId(strDomainId);
             topKeyword.setServiceType(WebPublic.SERVICE_TYPE_KB);
             topKeyword.setSystemType(strTopKeywordType);
             topKeyword.setEndNo(iTopKeywordCount);

             list = (List) sqlMap.queryForList("TopKeyword.selectTopKeywordListByDomainId", topKeyword);
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }
         return list;
     }
     
     /**
      * 사용자 아이디로 최근 지식 조회목록 개수를 가져오는 메소드이다.
      * @param accountId
      * @return
      */
     public int selectKbViewLogListCount(String domainId, String accountId)
     {    	 
    	 int iResult = 0;
    	 try
         {
        	 KbHelper obj = new KbHelper();
        	 obj.setDomainId(domainId);
        	 obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
        	 obj.setCreatedBy(accountId);
        	 obj.setCurrentDate(DateUtil.getCurrDate("yyyyMMddHHmmss"));
        	 
        	 iResult = ((Integer)sqlMap.queryForObject("KbHelper.selectKbViewLogListCount", obj)).intValue();
         }
         catch (Exception e)
         {
             logger.error(e.getMessage());
         }         
         return iResult;
     }
     
     /**
      * 사용자 아이디로 최근 지식 조회목록을 가져오는 메소드이다.
      * @param kbId
      * @return
      */
     public List selectKbViewLogList(int pageNo, int rowsPerPage, String domainId, String accountId)
     {
    	 List list = null;
         try
         {
        	 KbHelper obj = new KbHelper();
        	 obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
        	 obj.setDomainId(domainId);
        	 obj.setCreatedBy(accountId);
        	 obj.setCurrentDate(DateUtil.getCurrDate("yyyyMMddHHmmss"));
        	 obj.setStartNo((pageNo-1) * rowsPerPage + 1);
        	 obj.setEndNo(pageNo * rowsPerPage);
        	 obj.setRowsPerPage(rowsPerPage);
        	 
        	 list = (List)sqlMap.queryForList("KbHelper.selectKbViewLogList", obj);
         }
         catch (Exception e)
         {
             logger.error(e.getMessage());
         }
         return list;
     }
     
     /**
      * 이슈지식 리스트를 가져오는 메소드이다.
      * @param pageNo
      * @param rowsPerPage
      * @param domainId
      * @return
      */
     public List selectIssueKbList(int pageNo, int rowsPerPage, String domainId)
     {
     	return selectIssueKbList(pageNo, rowsPerPage, domainId, null, null);
     }
     
     /**
      * 최근에 등록된 이슈 지식리스트를 가져오는 메소드이다.
      * @param strDomainId
      * @param iCount
      * @return
      */
     public List selectIssueKbList(int pageNo, int rowsPerPage, String domainId, String condition, String searchText)
     {
         List listRet = null;
         try
         {
             String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
             KbHelper obj = new KbHelper();
             obj.setDomainId(domainId);
             obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
             obj.setCurrentDate(currDate);
             obj.setStartNo((pageNo-1) * rowsPerPage + 1);
             obj.setEndNo(pageNo * rowsPerPage);
             obj.setRowsPerPage(rowsPerPage);
             obj.setCondition(condition);
             if (searchText != null)
             {
                 searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
                 searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
             }
             obj.setSearchText(searchText);
             
             listRet = (List) sqlMap.queryForList("KbHelper.selectIssueKbList", obj);
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }
         return listRet;
     }
     
     /**
      * 이슈 지식리스트 개수를 가져오는 메소드이다.
      * @param domainId
      * @param condition
      * @param searchText
      * @return
      */
     public int selectIssueKbListCount(String domainId, String condition, String searchText)
     {
         int iResult = 0;
         try
         {
         	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
             KbHelper obj = new KbHelper();
             obj.setDomainId(domainId);  
             obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
             obj.setCurrentDate(currDate);
        	 	obj.setCondition(condition);
        	 	if (searchText != null)
        	 	{
 	       	 	searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
 	       	 	searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
        	 	}
        	 	obj.setSearchText(searchText);
             
             iResult = ((Integer)sqlMap.queryForObject("KbHelper.selectIssueKbListCount", obj)).intValue();
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }
         return iResult;
     }
     
     /**
      * 이슈지식의 이전글 가져오는 메소드이다.
      * @param domainId
      * @param kbId
      * @param createdDate
      * @return
      */
     public KbHelper selectPrevIssueKb(String domainId, String kbId, int issueOrder)
     {
         KbHelper kbHelperObj = null;
         try
         {
         	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
            KbHelper obj = new KbHelper();
            obj.setDomainId(domainId);
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setCurrentDate(currDate);
            obj.setIssueOrder(issueOrder);
         	obj.setKbId(kbId);
             
         	kbHelperObj = (KbHelper) sqlMap.queryForObject("KbHelper.selectPrevIssueKb", obj);
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }
         return kbHelperObj;
     }
     
     /**
      * 이슈지식의 다음글 가져오는 메소드이다.
      * @param domainId
      * @param kbId
      * @param createdDate
      * @return
      */
     public KbHelper selectNextIssueKb(String domainId, String kbId, int issueOrder)
     {
         KbHelper kbHelperObj = null;
         try
         {
         	String currDate = DateUtil.getCurrDate("yyyyMMddHHmmss");
            KbHelper obj = new KbHelper();
            obj.setDomainId(domainId);
            obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
            obj.setCurrentDate(currDate);
         	obj.setIssueOrder(issueOrder);
         	obj.setKbId(kbId);
             
         	kbHelperObj = (KbHelper) sqlMap.queryForObject("KbHelper.selectNextIssueKb", obj);
         }
         catch (SQLException e)
         {
             logger.error(e.getMessage());
         }
         return kbHelperObj;
     }
}
